package com.dc.adaptor.persistance;

import static com.dc.adaptor.persistance.Const.CONTENT_ID;
import static com.dc.adaptor.persistance.Const.FLAG;
import static com.dc.adaptor.persistance.Const.GLOBALBUSINESSID;
import static com.dc.adaptor.persistance.Const.ID;
import static com.dc.adaptor.persistance.Const.MESSAGEID;
import static com.dc.adaptor.persistance.Const.MESSAGE_ID;
import static com.dc.adaptor.persistance.Const.RECEIVEID;
import static com.dc.adaptor.persistance.Const.SERVICETYPE;
import static com.dc.adaptor.persistance.Const.SOURCEID;
import static com.dc.adaptor.persistance.Const.TABLE_CHECKINFO;
import static com.dc.adaptor.persistance.Const.TABLE_FAULTINFO;
import static com.dc.adaptor.persistance.Const.TABLE_MAINCHECKINFO;
import static com.dc.adaptor.persistance.Const.TABLE_RECV_PACK;
import static com.dc.adaptor.persistance.Const.TABLE_SEND_PACK;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.pentaho.di.core.RowMetaAndData;
import org.pentaho.di.core.database.Database;
import org.pentaho.di.core.exception.KettleDatabaseException;
import org.pentaho.di.core.row.RowMeta;
import org.pentaho.di.core.row.RowMetaInterface;
import org.pentaho.di.core.row.ValueMeta;
import org.pentaho.di.core.row.ValueMetaInterface;
import org.pentaho.di.repository.Repository;

import com.dc.adaptor.persistance.bean.SipCheckInfo;
import com.dc.adaptor.persistance.bean.SipContent;
import com.dc.adaptor.persistance.bean.SipFaultInfo;
import com.dc.adaptor.persistance.bean.SipMainCheckInfo;
import com.dc.adaptor.persistance.bean.SipMapping;
import com.dc.adaptor.persistance.bean.SipRecvPack;
import com.dc.adaptor.persistance.bean.SipSendPack;
import com.dc.ws.SipDataReceiver;

public class GT3DbHandler extends DatabaseHandler {
	
	public GT3DbHandler(Repository repository){
		super(repository);
	}
	
	public GT3DbHandler(){
		super();
	}
	
	public void updateReturnMessage(final String sourceId,String returnoCode,String returnMessage)throws Exception{
			doInConnection(new ConnectionCallback<String>(){
				@Override
				public String doInConnection(Database db) throws Exception {
					
					StringBuilder sbSql = new StringBuilder("UPDATE ").append(
							TABLE_SEND_PACK).append(" SET ").append(FLAG).append("=? ").append("WHERE ")
							.append(" TYPE='0' AND ").append(MESSAGE_ID).append("= ?");
					RowMetaInterface params = new RowMeta();
					params.addValueMeta(new ValueMeta(FLAG, ValueMetaInterface.TYPE_STRING, 1, 0));
					params.addValueMeta(new ValueMeta(MESSAGE_ID, ValueMetaInterface.TYPE_STRING, 32, 0));					
					db.execStatement(sbSql.toString(), params, new Object[] {"3",sourceId});	
					return null;
				}
			});
	}
	
	public SipRecvPack findReceivePackByMessageId(final String messageId) throws Exception{
			return doInConnection(new ConnectionCallback<SipRecvPack>(){
				@Override
				public SipRecvPack doInConnection(Database db) throws Exception {
					StringBuilder sbSql = new StringBuilder("SELECT * FROM ").append(TABLE_RECV_PACK).append(" WHERE ").append(MESSAGE_ID).append("=?");
					RowMetaInterface params = new RowMeta();
					params.addValueMeta(new ValueMeta(MESSAGE_ID, ValueMetaInterface.TYPE_STRING, 32, 0));
					RowMetaAndData md = db.getOneRow(sbSql.toString(), params, new Object[] { messageId });
					if(md!= null){
						Object[] dataRow = md.getData();
						if (dataRow == null){
							return null;
						}
						SipRecvPack pack = new SipRecvPack();
						if(loadContent){
							SipContent sipContent = findContent(md.getString(CONTENT_ID, null));
							pack.setSipContent(sipContent);
						}
						convertMetaDataToRecvPack(md,pack);
						return pack;
					}
					return null;
				}			
			});
	}
	
	// add by xueminzhang 2012-7-26
	public void saveCheckInfo(final SipCheckInfo sipCheckInfo) throws Exception{
		doInConnection(new ConnectionCallback<String>(){
			public String doInConnection(Database db) throws Exception {
				Object[] rowData = new Object[]{
						sipCheckInfo.getId(),
						sipCheckInfo.getM_ID(),
						sipCheckInfo.getServiceType(),
						sipCheckInfo.getSourceID(),						
						sipCheckInfo.getReceiveID(),
						sipCheckInfo.getGlobalBusinessID(),
						sipCheckInfo.getMessageID(),
						sipCheckInfo.getFlag(),
						sipCheckInfo.getSendFlag()
					};
				RowMetaInterface rowMeta = getCheckInfoFields();
				insert(TABLE_CHECKINFO,rowMeta,rowData);	
				db.commit();
				return null;
			}
			
		});
	}
	// add by xueminzhang 2012-7-26
	protected void insert(String tableName,RowMetaInterface rowMeta,Object[] rowData) throws Exception{
		db.prepareInsert(rowMeta, tableName);
		db.setValuesInsert(rowMeta, rowData);
		db.insertRow();
		db.closeInsert();
	}
	// add by xueminzhang 2012-7-26
	public RowMetaInterface getCheckInfoFields() throws KettleDatabaseException {
		RowMetaInterface recvPackMeta = new RowMeta();
		ValueMetaInterface v;
		v = new ValueMeta(ID, ValueMetaInterface.TYPE_STRING, 36, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("M_ID", ValueMetaInterface.TYPE_STRING, 36, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta(SERVICETYPE, ValueMetaInterface.TYPE_STRING, 4, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta(SOURCEID, ValueMetaInterface.TYPE_STRING, 4, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta(RECEIVEID, ValueMetaInterface.TYPE_STRING, 4, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta(GLOBALBUSINESSID, ValueMetaInterface.TYPE_STRING, 24, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta(MESSAGEID, ValueMetaInterface.TYPE_STRING, 24, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta(FLAG, ValueMetaInterface.TYPE_STRING, 2, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("sendFlag", ValueMetaInterface.TYPE_STRING, 1, 0);
		recvPackMeta.addValueMeta(v);
		return recvPackMeta;
	}
	
	// add by xueminzhang 2012-7-26
	public void saveMainCheckInfo(final SipMainCheckInfo sipMainCheckInfo) throws Exception{
		doInConnection(new ConnectionCallback<String>(){
			public String doInConnection(Database db) throws Exception {
				Object[] rowData = new Object[]{
						sipMainCheckInfo.getId(),
						sipMainCheckInfo.getMessageID(),
						sipMainCheckInfo.getCheckDate(),
						sipMainCheckInfo.getCheckResult(),
						sipMainCheckInfo.getSendFlag(),
						sipMainCheckInfo.getSourceId(),
						sipMainCheckInfo.getCheckGlobalBusinessID()
					};
				RowMetaInterface rowMeta = getMainCheckInfoFields();
				insertMainCheckInfo(TABLE_MAINCHECKINFO,rowMeta,rowData);
				db.commit();
				return null;
			}
			
		});
	}
	// add by xueminzhang 2012-7-26
	private void insertMainCheckInfo(String SIP_MAINCHECKINFO,RowMetaInterface rowMeta,Object[] rowData) throws Exception{
		db.prepareInsert(rowMeta, SIP_MAINCHECKINFO);
		db.setValuesInsert(rowMeta, rowData);
		db.insertRow();
		db.closeInsert();
	}
	// add by xueminzhang 2012-7-26
	public RowMetaInterface getMainCheckInfoFields() throws KettleDatabaseException {
		RowMetaInterface recvPackMeta = new RowMeta();
		ValueMetaInterface v;
		v = new ValueMeta("ID", ValueMetaInterface.TYPE_STRING, 36, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("MESSAGEID", ValueMetaInterface.TYPE_STRING, 64, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("CHECKDATE", ValueMetaInterface.TYPE_DATE);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("CHECKRESULT", ValueMetaInterface.TYPE_STRING, 20, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("SENDFLAG", ValueMetaInterface.TYPE_STRING, 1, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("SOURCEID", ValueMetaInterface.TYPE_STRING, 4, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("CHECKGLOBALBUSINESSID", ValueMetaInterface.TYPE_STRING, 64, 0);
		recvPackMeta.addValueMeta(v);
		return recvPackMeta;
	}
	
	public String findRouterId(final String bwlx) throws Exception{
		return doInConnection(new ConnectionCallback<String>(){
			@Override
			public String doInConnection(Database db) throws Exception{
				StringBuilder sbSql = new StringBuilder("SELECT ").append(" ROUTERID " )
				.append(" FROM ").append(" SIP_PACKAGEMAPPING ").append(
						" WHERE ").append(" SERVICEID ").append(" = ?");
				RowMetaInterface params = new RowMeta();
				params.addValueMeta(new ValueMeta("SERVICEID", ValueMetaInterface.TYPE_STRING, 32, 0));
				RowMetaAndData md = db.getOneRow(sbSql.toString(), params, new Object[] { bwlx });
				if(md != null){
				    Object[] dataRow = md.getData();
					if (dataRow == null){
						return null;
					}
					return md.getString("ROUTERID", null);
				
				}
				return null;
			}			
		});
	}
	
	// add by xueminzhang 2012-8-2-20:16
	public List<SipMainCheckInfo> findMainCheckInfo() throws Exception{
		return (List<SipMainCheckInfo>) doInConnection(new ConnectionCallback<List<SipMainCheckInfo>>(){
			@Override
			public List<SipMainCheckInfo> doInConnection(Database db) throws Exception {
 				StringBuilder sbSql = new StringBuilder("SELECT * FROM ").append(TABLE_MAINCHECKINFO).append(" where sendflag='N'");
				List<SipMainCheckInfo> list = new ArrayList<SipMainCheckInfo>();
				ResultSet rs = null;
				try {
					rs = db.openQuery(sbSql.toString());
					while(rs.next()){
						SipMainCheckInfo smci = new SipMainCheckInfo();
						smci.setId(rs.getString("id"));
						smci.setMessageID(rs.getString("MessageID"));
						smci.setCheckResult(rs.getString("CheckResult"));
						smci.setCheckDate(rs.getDate("CheckDate"));
						smci.setSendFlag(rs.getString("SendFlag"));
						smci.setSourceId(rs.getString("SourceId"));
						list.add(smci);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally{
					try {
						db.closeQuery(rs);
					} catch (KettleDatabaseException e) {
						e.printStackTrace();
					}
				}
				return list;
			}			
		});
	}
	
	// add by xueminzhang 2012-8-2-20:16
	public List<SipCheckInfo> findCheckInfo(final String id) throws Exception{
		return (List<SipCheckInfo>) doInConnection(new ConnectionCallback<List<SipCheckInfo>>(){
			@Override
			public List<SipCheckInfo> doInConnection(Database db) throws Exception {
				StringBuilder sbSql = new StringBuilder("SELECT * FROM ").append(TABLE_CHECKINFO).append(" where sendflag='N' and M_ID=?");
				RowMetaInterface params = new RowMeta();
				List<SipCheckInfo> list = new ArrayList<SipCheckInfo>();
				ResultSet rs = null;
				try {
					params.addValueMeta(new ValueMeta("M_ID", ValueMetaInterface.TYPE_STRING, 36, 0));
					rs =db.openQuery(sbSql.toString(), params, new Object[] {id});
					while(rs.next()){
						SipCheckInfo sci = new SipCheckInfo();
						sci.setId(rs.getString("id"));
						sci.setServiceType(rs.getString("serviceType"));
						sci.setSourceID(rs.getString("sourceID"));
						sci.setReceiveID(rs.getString("ReceiveID"));
						sci.setGlobalBusinessID(rs.getString("globalBusinessID"));
						sci.setMessageID(rs.getString("MessageID"));
						sci.setFlag(rs.getString("flag"));
						sci.setM_ID(rs.getString("M_ID"));
						sci.setSendFlag(rs.getString("sendFlag"));
						list.add(sci);
					}
				} catch (Exception e) {
					e.printStackTrace();
				}finally{
					try {
						db.closeQuery(rs);
					} catch (KettleDatabaseException e) {
						e.printStackTrace();
					}
				}
				return list;
			}			
		});
	}
	
	// add by xueminzhang 2012-8-3
	public void updateMainSendFlag(final String id)throws Exception{
		doInConnection(new ConnectionCallback<String>(){
			@Override
			public String doInConnection(Database db) throws Exception {
				StringBuilder sbSql = new StringBuilder("UPDATE ").append(
						TABLE_MAINCHECKINFO).append(" SET ").append("sendflag = 'Y' ").append("WHERE id = ? ");
				RowMetaInterface params = new RowMeta();
				params.addValueMeta(new ValueMeta(id, ValueMetaInterface.TYPE_STRING, 36, 0));
				db.execStatement(sbSql.toString(), params, new Object[] {id});	
				return "ok";
			}
		});
	}
	
	
	// add by xueminzhang 2012-8-3
	public void updateSendFlag(final String id)throws Exception{
		doInConnection(new ConnectionCallback<String>(){
			@Override
			public String doInConnection(Database db) throws Exception {
				StringBuilder sbSql = new StringBuilder("UPDATE ").append(
						TABLE_CHECKINFO).append(" SET ").append("sendflag = 'Y' ").append("WHERE M_ID = ? ");
				RowMetaInterface params = new RowMeta();
				params.addValueMeta(new ValueMeta("M_ID", ValueMetaInterface.TYPE_STRING, 36, 0));
				db.execStatement(sbSql.toString(), params, new Object[] {id});	
				return "ok";
			}
		});
	}
	
	// add by xueminzhang 2012-8-6
	public List<SipSendPack> findRepeatSetFlag(final String jobname,final long times,final int counts) throws Exception{
		return doInConnection(new ConnectionCallback<List<SipSendPack>>(){
			@Override
			public List<SipSendPack> doInConnection(Database db) throws Exception {
				StringBuilder sbSql = new StringBuilder("SELECT * FROM ").append(TABLE_SEND_PACK).append(" WHERE ").append(" FLAG <> '3' AND TYPE='0' AND SEND_COUNT < "+counts+" AND JOB_NAME = ? AND SEND_TIME < ?");
				RowMetaInterface params = new RowMeta();
				List<SipSendPack> list = new  ArrayList<SipSendPack>();
				ResultSet rs = null ;
				try {
					Calendar calendar = Calendar.getInstance();
					calendar.add(Calendar.SECOND, (int)times*-1);
					Date date = calendar.getTime();
					params.addValueMeta(new ValueMeta("JOB_NAME", ValueMetaInterface.TYPE_STRING, 64, 0));
					params.addValueMeta(new ValueMeta("SEND_TIME", ValueMetaInterface.TYPE_DATE));
					RowMetaAndData md = new RowMetaAndData(db.getQueryFields(sbSql.toString(), true, params, new Object[] {jobname,date}));					
					rs =db.openQuery(sbSql.toString(), params, new Object[] {jobname,date});
					Object[] dataRow = null;				
					do {
						dataRow = db.getRow(rs);
						if (dataRow != null) {
							md.setData(dataRow);
							SipSendPack sendpack = new SipSendPack();	
							convertMetaDataToSendPack(md, sendpack);
							list.add(sendpack);
						}
					} while (dataRow != null);
				} catch (Exception e) {
					throw e;
				}finally {
					try {
						db.closeQuery(rs);
					} catch (KettleDatabaseException e) {
						e.printStackTrace();
					}
				}
				return list;
			}			
		});
	}
	
	// add by xueminzhang ; 2012-8-9 AM 10:32 ; description faultinfo data synchronization
	public List<SipFaultInfo> findFaultInfo() throws Exception{
		return (List<SipFaultInfo>) doInConnection(new ConnectionCallback<List<SipFaultInfo>>(){
			@Override
			public List<SipFaultInfo> doInConnection(Database db) throws Exception {
 				StringBuilder sbSql = new StringBuilder("SELECT * FROM ").append(TABLE_FAULTINFO).append(" where FI_TBZT='0'");
				List<SipFaultInfo> list = new ArrayList<SipFaultInfo>();
				ResultSet rs = null;
				try {
					rs = db.openQuery(sbSql.toString());
					while(rs.next()){
						SipFaultInfo bean = new SipFaultInfo();
						bean.setId(rs.getString("FI_NM"));
						bean.setName(rs.getString("FI_YCMC"));
						bean.setDescription(rs.getString("FI_YCMS"));
						bean.setFaultDate(rs.getDate("FI_FSSJ"));
						list.add(bean);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally{
					try {
						db.closeQuery(rs);
					} catch (KettleDatabaseException e) {
						e.printStackTrace();
					}
				}
				return list;
			}			
		});
	}
	
	// add by xuemin zhang ; date 2012-8-9 ; description update upload state.
	public void updateUploadflag(final String nm)throws Exception{
		doInConnection(new ConnectionCallback<String>(){
			@Override
			public String doInConnection(Database db) throws Exception {
				StringBuilder sbSql = new StringBuilder("UPDATE ").append(
						TABLE_FAULTINFO).append(" SET ").append("FI_TBZT = '1' ").append("WHERE FI_NM = ? ");
				RowMetaInterface params = new RowMeta();
				params.addValueMeta(new ValueMeta("FI_NM", ValueMetaInterface.TYPE_STRING, 32, 0));
				db.execStatement(sbSql.toString(), params, new Object[] {nm});	
				return "ok";
			}
		});
	}
	
	public void saveMapping(final SipMapping sipMapping) throws Exception{
		
		 doInConnection(new ConnectionCallback<String>(){

			@Override
			public String doInConnection(Database db) throws Exception {
				StringBuilder sbSql = new StringBuilder("SELECT * FROM ").append("sip_packagemapping").append(" WHERE ").append(ID).append("=?");
				RowMetaInterface params = new RowMeta();
				params.addValueMeta(new ValueMeta(ID, ValueMetaInterface.TYPE_STRING, 32, 0));
				RowMetaAndData md = db.getOneRow(sbSql.toString(), params, new Object[] { sipMapping.getId() });
				
				if(md != null){
					Object[] dataRow = md.getData();
					if (dataRow == null){
						Object[] rowData = new Object[]{
								sipMapping.getId(),
								sipMapping.getServiceId(),
								sipMapping.getBusinessType(),
								sipMapping.getResbusinessType(),
								sipMapping.getSourceId(),
								sipMapping.getDestinationId(),
								sipMapping.getDestinationAppId(),
								sipMapping.getName(),
								sipMapping.getRouterId()
						};
						insert("SIP_PACKAGEMAPPING", getMappingFields(), rowData);
						return null;
					}else{
						sbSql = new StringBuilder("UPDATE SIP_PACKAGEMAPPING set BUSINESSTYPE=?,RESBUSINESSTYPE=?,SOURCEID=?,DESTINATIONID=?,DESTINATIONAPPID=?,ROUTERID=? where ")
								.append(ID).append("= ?");
						params = new RowMeta();
						params.addValueMeta(new ValueMeta("BUSINESSTYPE", ValueMetaInterface.TYPE_STRING, 32, 0));
						params.addValueMeta(new ValueMeta("RESBUSINESSTYPE", ValueMetaInterface.TYPE_STRING, 32, 0));
						params.addValueMeta(new ValueMeta("SOURCEID", ValueMetaInterface.TYPE_STRING, 32, 0));
						params.addValueMeta(new ValueMeta("DESTINATIONID", ValueMetaInterface.TYPE_STRING, 32, 0));
						params.addValueMeta(new ValueMeta("DESTINATIONAPPID", ValueMetaInterface.TYPE_STRING, 32, 0));
						params.addValueMeta(new ValueMeta("ROUTERID", ValueMetaInterface.TYPE_STRING, 32, 0));
						params.addValueMeta(new ValueMeta("ID", ValueMetaInterface.TYPE_STRING, 32, 0));
						db.execStatement(sbSql.toString(), params, new Object[] { sipMapping.getBusinessType(),sipMapping.getResbusinessType(), sipMapping.getSourceId(),sipMapping.getDestinationId(),
							sipMapping.getDestinationAppId(),sipMapping.getRouterId(), sipMapping.getId() });
						SipDataReceiver.updateCache(sipMapping.getServiceId(), sipMapping.getRouterId());
					}				
				}
				return null;
			}
			
		});
	}
	
	public RowMetaInterface getMappingFields() throws KettleDatabaseException {
		RowMetaInterface recvPackMeta = new RowMeta();
		ValueMetaInterface v;
		v = new ValueMeta(ID, ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("SERVICEID", ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("BUSINESSTYPE", ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("RESBUSINESSTYPE", ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("SOURCEID", ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("DESTINATIONID", ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("DESTINATIONAPPID", ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("NAME", ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);
		v = new ValueMeta("ROUTERID", ValueMetaInterface.TYPE_STRING, 32, 0);
		recvPackMeta.addValueMeta(v);	
		return recvPackMeta;
	}
	
	
}
